-- Copyright (c) Microsoft Corporation. All rights reserved.

/****** Object:  StoredProcedure [dbo].[prc_iiRegisterArtifact]    Script Date: 05/16/2007 09:15:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[prc_iiRegisterArtifact]
  @IsTfs BIT,
  @Uri NVARCHAR(400),
  @Id INT OUTPUT
AS BEGIN
  SELECT @Id = Id FROM Artifacts WHERE IsTfs=@IsTfs AND Uri=@Uri;
  IF @@ROWCOUNT = 0 BEGIN
    INSERT INTO Artifacts(IsTfs, Uri) VALUES(@IsTfs, @Uri);
    SELECT @Id = @@IDENTITY;
  END
END
GO

/****** Object:  StoredProcedure [dbo].[prc_iiDeferLink]    Script Date: 05/16/2007 09:15:40 ******/
CREATE PROC [dbo].[prc_iiDeferLink]
  @SessionId INT,
  @IsTfs BIT,
  @Source NVARCHAR(400),
  @Target NVARCHAR(400),
  @LinkType NVARCHAR(400),
  @LinkData NVARCHAR(MAX)
AS BEGIN
  DECLARE @SourceId INT, @TargetId INT;
  EXEC prc_iiRegisterArtifact @IsTfs, @Source, @SourceId OUTPUT;
  EXEC prc_iiRegisterArtifact @IsTfs, @Target, @TargetId OUTPUT;

  -- Update existing link
  UPDATE DeferredLinks SET LinkData=@LinkData 
    WHERE SessionId=@SessionId AND SourceId=@SourceId AND TargetId=@TargetId AND LinkType=@LinkType;
  IF @@ROWCOUNT = 0 BEGIN
    INSERT INTO DeferredLinks(SessionId, SourceId, TargetId, LinkType, LinkData)
      VALUES(@SessionId, @SourceId, @TargetId, @LinkType, @LinkData);
  END
END
GO

/****** Object:  StoredProcedure [dbo].[prc_iiRemoveDeferredLink]    Script Date: 05/22/2007 10:46:58 ******/
CREATE PROC [dbo].[prc_iiRemoveDeferredLink]
  @SessionId INT,
  @IsTfs BIT,
  @LinkType NVARCHAR(400),
  @Source NVARCHAR(400),
  @Target NVARCHAR(400)
AS BEGIN
  DELETE FROM dl FROM DeferredLinks dl
    INNER JOIN Artifacts sa ON dl.SourceId=sa.Id
    INNER JOIN Artifacts ta ON dl.TargetId=ta.Id
  WHERE
    dl.SessionId=@SessionId AND dl.LinkType=@LinkType
    AND sa.Uri=@Source AND sa.IsTfs=@IsTfs AND ta.Uri=@Target AND ta.IsTfs=@IsTfs
END
GO

/****** Object:  StoredProcedure [dbo].[prc_iiGetDeferredLinks]    Script Date: 05/22/2007 10:50:32 ******/
CREATE PROC [dbo].[prc_iiGetDeferredLinks]
  @SessionId INT,
  @Uri NVARCHAR(400),
  @IsTfs BIT
AS BEGIN
  SELECT ta.Uri, dl.LinkType, dl.LinkData
  FROM DeferredLinks dl
      INNER JOIN Artifacts sa ON dl.SourceId=sa.Id
      INNER JOIN Artifacts ta ON dl.TargetId=ta.Id
  WHERE dl.SessionId=@SessionId AND sa.Uri=@Uri AND sa.IsTfs=@IsTfs
END
GO

/****** Object:  StoredProcedure [dbo].[prc_iiGetDeferredLinkSources]    Script Date: 05/22/2007 10:52:46 ******/
CREATE PROC [dbo].[prc_iiGetDeferredLinkSources]
  @SessionId INT
AS BEGIN
  SELECT IsTfs, Uri FROM Artifacts a
  WHERE EXISTS (SELECT * FROM DeferredLinks dl WHERE dl.SourceId=a.Id AND dl.SessionId=@SessionId)
END
GO
